package dao.impl;

import dao.MenuDao;
import pojo.Menu;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 菜单接口的实现类
 * @author 叶军军
 */
public class MenuDaoImpl implements MenuDao {

    /**
     *菜单的分页功能
     */
    @Override
    public PageBean menuPageList(PageBean pb, String ename) {
        List<Menu> list=new ArrayList<Menu>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+="and  me_name LIKE '%"+ename+"%'";
            }
            String sql=" SELECT * FROM (" +
                    "  SELECT ROWNUM r,menu.* FROM menu " +
                    "  WHERE 1=1 "+s+" and ROWNUM<=?" +
                    " )WHERE r>?";
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while(rs.next()){
                Menu menu=new Menu();
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menu.setMeRool(rs.getInt("me_rool"));
                list.add(menu);
            }
            pb.setList(list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return pb;
    }

    /**
     *统计菜单总数
     */
    @Override
    public int count(String ename) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+="  and  me_name LIKE '%"+ename+"%' ";
            }
            String sql="select count(*) from menu  where 1=1 "+s;
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return count;
    }

    /**
     * 查询所有菜单
     */
    @Override
    public List<Menu> queryMenu() {
        List<Menu> menus=new ArrayList<Menu>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select * from menu where me_rool=1";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Menu menu=new Menu();
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menu.setMeRool(rs.getInt("me_rool"));
                menus.add(menu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return menus;
    }

    /**
     *根据菜单名称修改菜单权限
     */
    @Override
    public int updateMenuRoolByName(String name, int rool) {
        return BaseDao.cud("update menu set me_rool=? where me_name=?",rool,name);
    }

    /**
     *查询顶级菜单
     */
    @Override
    public List<Menu> querySupMenu() {
        List<Menu> menus=new ArrayList<Menu>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select * from menu where me_high=100";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Menu menu=new Menu();
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menu.setMeRool(rs.getInt("me_rool"));
                menus.add(menu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return menus;
    }

    /**
     *新增菜单
     */
    @Override
    public int inseMenu(Menu menu) {
        return BaseDao.cud("insert into menu values(sequence_menu.nextval,?,?,?,?)",menu.getMeName(),menu.getMeUrl(),menu.getMeRool(),menu.getMeHigh());
    }

    /**
     *根据菜单id查询菜单信息
     */
    @Override
    public Menu queryMenuById(int id) {
        Menu menu=new Menu();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select * from menu WHERE me_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menu.setMeRool(rs.getInt("me_rool"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return menu;
    }

    /**
     * 修改菜单
     */
    @Override
    public int modifMenu(Menu menu) {
        return BaseDao.cud("update menu set me_name=?,me_url=?,me_high=? where me_id=?",menu.getMeName(),menu.getMeUrl(),menu.getMeHigh(),menu.getMeId());
    }

    /**
     *删除菜单
     */
    @Override
    public int deleMenu(int id) {
        return BaseDao.cud("delete from menu where me_id=?",id);
    }

    /**
     * 根据角色id查询角色拥有的菜单
     */
    @Override
    public List<Menu> queryMenuByRoId(int roleId) {
        List<Menu> menus=new ArrayList<Menu>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=BaseDao.getConnection();
            String sql="select distinct me.* from menu me,romess rm" +
                    " where me.me_rool=1 and me.me_Id=rm.menu_id and rm.role_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,roleId);
            rs=ps.executeQuery();
            while(rs.next()){
                Menu menu=new Menu();
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menu.setMeRool(rs.getInt("me_rool"));
                menus.add(menu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return menus;
    }


}
